﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

/// <summary>
/// ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    public class x2003
    {
        #region Excel2003
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xls)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLS(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
                ISheet sheet = hssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xls)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcelForXLS(DataTable dt, string file)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet = hssfworkbook.CreateSheet("Test");

            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            hssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 获取单元格类型(xls)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLS(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion
    }

    public class x2007
    {
        #region Excel2007
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLSX(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
                ISheet sheet = xssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="dt">数据DataTable</param>
        /// <param name="file">地址</param>
        /// <param name="title">标题</param>
        public static void TableToExcelForXLSX(DataTable dt, string file, string title, int col)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Test");

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));

            //设置单元格样式
            ICellStyle style = xssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;

            //新建一个字体样式对象
            IFont font = xssfworkbook.CreateFont();
            //设置字体加粗样式
            font.Boldweight = short.MaxValue;
            font.FontName = "宋体";
            font.FontHeight = 18;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);

            ICellStyle style2 = xssfworkbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.Center;
            style2.VerticalAlignment = VerticalAlignment.Center;
            //设置标题背景颜色
            style2.FillBackgroundColor = 30;
            //表头
            if (title.Length > 0)
            {
                //IRow row0 = sheet.CreateRow(0).CreateCell(0);
                //ICell cell0 = row0.CreateCell(0);

                ICell cell0 = sheet.CreateRow(0).CreateCell(0);
                cell0.SetCellValue(title);
                cell0.CellStyle = style;
                cell0.Row.Height = 500;


            }
            IRow row = sheet.CreateRow(1);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
                cell.Row.Height = 500;
                cell.CellStyle = style2;
                sheet.AutoSizeColumn(0);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 2);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                    cell.Row.Height = 400;
                    cell.CellStyle = style2;
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }



        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="dt">数据DataTable</param>
        /// <param name="file">地址</param>
        /// <param name="title">标题</param>
        public static void TableToExcelForXLSX2(DataTable dt1, DataTable dt2, string file, string sheetName, string title, int col)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet(sheetName);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));

            //设置单元格样式
            ICellStyle style = xssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            //新建一个字体样式对象
            IFont font = xssfworkbook.CreateFont();
            //设置字体加粗样式
            font.Boldweight = short.MaxValue;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);

            //表头
            if (title.Length > 0)
            {
                //IRow row0 = sheet.CreateRow(0).CreateCell(0);
                //ICell cell0 = row0.CreateCell(0);
                ICell cell0 = sheet.CreateRow(0).CreateCell(0);
                cell0.SetCellValue(title);
                cell0.CellStyle = style;
            }

            IRow row = sheet.CreateRow(1);
            for (int i = 0; i < dt1.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt1.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt1.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt1.Rows[i][j].ToString());
                }
            }

            IRow row2 = sheet.CreateRow(dt1.Rows.Count + 1);
            for (int i = 0; i < dt2.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt2.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                IRow row3 = sheet.CreateRow(dt2.Rows.Count + dt1.Rows.Count + i + 1);
                for (int j = 0; j < dt2.Columns.Count; j++)
                {
                    ICell cell = row3.CreateCell(j);
                    cell.SetCellValue(dt2.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }



        #endregion



        /// <summary>
        /// 将记录单导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="sqr">导出路径</param>
        /// <param name="sqr">诉求人</param>
        /// <param name="xb">性别</param>
        /// <param name="lxdh">联系电话</param>
        /// <param name="sqly">诉求来源</param>
        /// <param name="sqlx">诉求类型</param>
        /// <param name="sqfl">诉求分类</param>
        /// <param name="sqbh">诉求编号</param>
        /// <param name="blgh">办理工号</param>
        /// <param name="djsj">等级时间</param>
        /// <param name="zrdw">责任单位</param>
        /// <param name="sfcs">是否超时</param>
        /// <param name="zbsj">转办时间</param>
        /// <param name="bldw">办理单位</param>
        /// <param name="sqyq">申请延期</param>
        /// <param name="xbsj">限办时间</param>
        /// <param name="sqzt">诉求主题</param>
        /// <param name="nbyj">拟办意见</param>
        /// <param name="sqnr">诉求内容</param>
        /// <param name="bljg">办理结果</param>
        /// <param name="ldps">领导批示</param>
        /// <param name="ycbh">一次驳回</param>
        /// <param name="ecbh">二次驳回</param>
        /// <param name="ycdb">一次督办</param>
        /// <param name="ecdb">二次督办</param>
        /// <param name="myqk">满意情况</param>
        /// <param name="fkr">反馈人</param>
        /// <param name="fksj">反馈时间</param>
        public static void ToExcelJLD(string file, string sqr, string xb, string lxdh, string sqly, string sqlx, string sqfl, string sqbh, string blgh, string djsj, string zrdw, string sfcs, string zbsj, string bldw, string sqyq, string xbsj, string sqzt, string nbyj, string sqnr, string bljg, string ldps, string ycbh, string ecbh, string ycdb, string ecdb, string myqk, string fkr, string fksj)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();

            string a = "";
            if (!zrdw.Equals(""))
            {
                a = zrdw;
            }
            else 
            {
                a = "Text";
            }
            ISheet sheet = xssfworkbook.CreateSheet(a);

            sheet.SetColumnWidth(0, 10 * 256);
            sheet.SetColumnWidth(1, 18 * 256);
            sheet.SetColumnWidth(2, 10 * 256);
            sheet.SetColumnWidth(3, 18 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 18 * 256);

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
            sheet.AddMergedRegion(new CellRangeAddress(6, 6, 1, 5));
            sheet.AddMergedRegion(new CellRangeAddress(7, 7, 1, 5));
            sheet.AddMergedRegion(new CellRangeAddress(8, 8, 1, 5));
            sheet.AddMergedRegion(new CellRangeAddress(9, 9, 1, 5));
            sheet.AddMergedRegion(new CellRangeAddress(10, 10, 1, 5));
            sheet.AddMergedRegion(new CellRangeAddress(11, 11, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(11, 11, 4, 5));
            sheet.AddMergedRegion(new CellRangeAddress(12, 12, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(12, 12, 4, 5));

            #region 大标题样式
            //设置单元格样式1
            ICellStyle style = xssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font = xssfworkbook.CreateFont();
            //设置字体加粗样式
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            font.FontName = "宋体";
            font.FontHeight = 24;
            font.Color = HSSFColor.Red.Index;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);
            #endregion

            //表头
            ICell cell0 = sheet.CreateRow(0).CreateCell(0);
            cell0.SetCellValue("12345市长服务热线记录单");
            cell0.CellStyle = style;
            cell0.Row.Height = 800;



            #region 行标题样式
            //设置单元格样式1
            ICellStyle style1 = xssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            style1.Alignment = HorizontalAlignment.Center;
            style1.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font1 = xssfworkbook.CreateFont();
            //设置字体加粗样式
            font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            font1.FontName = "宋体";
            font1.FontHeight = 12;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.SetFont(font1);
            #endregion

            ICellStyle style2 = xssfworkbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.Center;
            style2.VerticalAlignment = VerticalAlignment.Center;
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //设置标题背景颜色
            //style2.FillBackgroundColor = 30;
            //新建一个字体样式对象
            IFont font2 = xssfworkbook.CreateFont();
            font2.FontName = "楷体";
            font2.FontHeight = 12;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style2.SetFont(font2);

            #region 第一行
            IRow row1 = sheet.CreateRow(1);
            ICell cell11 = row1.CreateCell(0);
            cell11.SetCellValue("诉求人");
            cell11.Row.Height = 480;
            cell11.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell12 = row1.CreateCell(1);
            cell12.SetCellValue(sqr);
            cell12.Row.Height = 480;
            cell12.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell13 = row1.CreateCell(2);
            cell13.SetCellValue("性别");
            cell13.Row.Height = 480;
            cell13.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell14 = row1.CreateCell(3);
            cell14.SetCellValue(xb);
            cell14.Row.Height = 480;
            cell14.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell15 = row1.CreateCell(4);
            cell15.SetCellValue("联系电话");
            cell15.Row.Height = 480;
            cell15.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell16 = row1.CreateCell(5);
            cell16.SetCellValue(lxdh);
            cell16.Row.Height = 480;
            cell16.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 第二行
            IRow row2 = sheet.CreateRow(2);
            ICell cell21 = row2.CreateCell(0);
            cell21.SetCellValue("诉求来源");
            cell21.Row.Height = 480;
            cell21.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell22 = row2.CreateCell(1);
            cell22.SetCellValue(sqly);
            cell22.Row.Height = 480;
            cell22.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell23 = row2.CreateCell(2);
            cell23.SetCellValue("诉求类型");
            cell23.Row.Height = 480;
            cell23.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell24 = row2.CreateCell(3);
            cell24.SetCellValue(sqlx);
            cell24.Row.Height = 480;
            cell24.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell25 = row2.CreateCell(4);
            cell25.SetCellValue("诉求分类");
            cell25.Row.Height = 480;
            cell25.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell26 = row2.CreateCell(5);
            cell26.SetCellValue(sqfl);
            cell26.Row.Height = 480;
            cell26.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 第三行
            IRow row3 = sheet.CreateRow(3);
            ICell cell31 = row3.CreateCell(0);
            cell31.SetCellValue("诉求编号");
            cell31.Row.Height = 480;
            cell31.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell32 = row3.CreateCell(1);
            cell32.SetCellValue(sqbh);
            cell32.Row.Height = 480;
            cell32.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell33 = row3.CreateCell(2);
            cell33.SetCellValue("办理工号");
            cell33.Row.Height = 480;
            cell33.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell34 = row3.CreateCell(3);
            cell34.SetCellValue(blgh);
            cell34.Row.Height = 480;
            cell34.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell35 = row3.CreateCell(4);
            cell35.SetCellValue("登记时间");
            cell35.Row.Height = 480;
            cell35.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell36 = row3.CreateCell(5);
            cell36.SetCellValue(djsj);
            cell36.Row.Height = 480;
            cell36.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 第四行
            IRow row4 = sheet.CreateRow(4);
            ICell cell41 = row4.CreateCell(0);
            cell41.SetCellValue("责任单位");
            cell41.Row.Height = 480;
            cell41.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell42 = row4.CreateCell(1);
            cell42.SetCellValue(zrdw);
            cell42.Row.Height = 480;
            cell42.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell43 = row4.CreateCell(2);
            cell43.SetCellValue("是否超期");
            cell43.Row.Height = 480;
            cell43.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell44 = row4.CreateCell(3);
            cell44.SetCellValue(sfcs);
            cell44.Row.Height = 480;
            cell44.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell45 = row4.CreateCell(4);
            cell45.SetCellValue("转办时间");
            cell45.Row.Height = 480;
            cell45.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell46 = row4.CreateCell(5);
            cell46.SetCellValue(zbsj);
            cell46.Row.Height = 480;
            cell46.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 第五行
            IRow row5 = sheet.CreateRow(5);
            ICell cell51 = row5.CreateCell(0);
            cell51.SetCellValue("办理单位");
            cell51.Row.Height = 480;
            cell51.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell52 = row5.CreateCell(1);
            cell52.SetCellValue(bldw);
            cell52.Row.Height = 480;
            cell52.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell53 = row5.CreateCell(2);
            cell53.SetCellValue("申请延期");
            cell53.Row.Height = 480;
            cell53.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell54 = row5.CreateCell(3);
            cell54.SetCellValue(sqyq);
            cell54.Row.Height = 480;
            cell54.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell55 = row5.CreateCell(4);
            cell55.SetCellValue("限办时间");
            cell55.Row.Height = 480;
            cell55.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell56 = row5.CreateCell(5);
            cell56.SetCellValue(xbsj);
            cell56.Row.Height = 480;
            cell56.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 诉求主题

            IRow row6 = sheet.CreateRow(6);
            ICell cell61 = row6.CreateCell(0);
            cell61.SetCellValue("诉求主题");
            cell61.Row.Height = 480;
            cell61.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell62 = row6.CreateCell(1);
            cell62.SetCellValue(sqzt);
            cell62.Row.Height = 480;
            cell62.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell63 = row6.CreateCell(2);
            cell63.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell64 = row6.CreateCell(3);
            cell64.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell65 = row6.CreateCell(4);
            cell65.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell66 = row6.CreateCell(5);
            cell66.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            #endregion

            #region 拟办意见
            IRow row7 = sheet.CreateRow(7);
            ICell cell71 = row7.CreateCell(0);
            cell71.SetCellValue("拟办意见");
            cell71.Row.Height = 480;
            cell71.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell72 = row7.CreateCell(1);
            cell72.SetCellValue(nbyj);
            cell72.Row.Height = 480;
            cell72.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell73 = row7.CreateCell(2);
            cell73.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell74 = row7.CreateCell(3);
            cell74.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell75 = row7.CreateCell(4);
            cell75.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell76 = row7.CreateCell(5);
            cell76.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 诉求内容
            IRow row8 = sheet.CreateRow(8);
            ICell cell81 = row8.CreateCell(0);
            cell81.SetCellValue("诉求内容");
            cell81.Row.Height = 100 * 20;
            cell81.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell82 = row8.CreateCell(1);
            cell82.SetCellValue(sqnr);
            cell81.Row.Height = 100 * 20;
            cell82.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell83 = row8.CreateCell(2);
            cell83.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell84 = row8.CreateCell(3);
            cell84.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell85 = row8.CreateCell(4);
            cell85.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell86 = row8.CreateCell(5);
            cell86.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 办理结果
            IRow row9 = sheet.CreateRow(9);
            ICell cell91 = row9.CreateCell(0);
            cell91.SetCellValue("办理结果");
            cell91.Row.Height = 100 * 20;
            cell91.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell92 = row9.CreateCell(1);
            cell92.SetCellValue(bljg);
            cell92.Row.Height = 100 * 20;
            cell92.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell93 = row9.CreateCell(2);
            cell93.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell94 = row9.CreateCell(3);
            cell94.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell95 = row9.CreateCell(4);
            cell95.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell96 = row9.CreateCell(5);
            cell96.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 领导批示
            IRow row10 = sheet.CreateRow(10);
            ICell cell101 = row10.CreateCell(0);
            cell101.SetCellValue("领导批示");
            cell101.Row.Height = 100 * 20;
            cell101.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell102 = row10.CreateCell(1);
            cell102.SetCellValue(ldps);
            cell102.Row.Height = 100 * 20;
            cell102.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell103 = row10.CreateCell(2);
            cell103.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell104 = row10.CreateCell(3);
            cell104.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell105 = row10.CreateCell(4);
            cell105.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell106 = row10.CreateCell(5);
            cell106.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 驳回
            IRow row11 = sheet.CreateRow(11);
            ICell cell111 = row11.CreateCell(0);
            cell111.SetCellValue("一次驳回");
            cell111.Row.Height = 100 * 20;
            cell111.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell112 = row11.CreateCell(1);
            cell112.SetCellValue(ycbh);
            cell112.Row.Height = 100 * 20;
            cell112.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell115 = row11.CreateCell(2);
            cell115.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell113 = row11.CreateCell(3);
            cell113.SetCellValue("二次驳回");
            cell113.Row.Height = 100 * 20;
            cell113.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell114 = row11.CreateCell(4);
            cell114.SetCellValue(ecbh);
            cell114.Row.Height = 100 * 20;
            cell114.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell116 = row11.CreateCell(5);
            cell116.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            #endregion

            #region 督办
            IRow row12 = sheet.CreateRow(12);
            ICell cell121 = row12.CreateCell(0);
            cell121.SetCellValue("一次督办");
            cell121.Row.Height = 100 * 20;
            cell121.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell122 = row12.CreateCell(1);
            cell122.SetCellValue(ycdb);
            cell122.Row.Height = 100 * 20;
            cell122.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell125 = row12.CreateCell(2);
            cell125.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell123 = row12.CreateCell(3);
            cell123.SetCellValue("二次督办");
            cell123.Row.Height = 100 * 20;
            cell123.CellStyle = style1;
            sheet.AutoSizeColumn(0);


            ICell cell124 = row12.CreateCell(4);
            cell124.SetCellValue(ecdb);
            cell124.Row.Height = 100 * 20;
            cell124.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell126 = row12.CreateCell(5);
            cell126.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion

            #region 最后一行
            IRow row13 = sheet.CreateRow(13);
            ICell cell131 = row13.CreateCell(0);
            cell131.SetCellValue("满意情况");
            cell131.Row.Height = 480;
            cell131.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell132 = row13.CreateCell(1);
            cell132.SetCellValue(myqk);
            cell132.Row.Height = 480;
            cell132.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell133 = row13.CreateCell(2);
            cell133.SetCellValue("反馈人");
            cell133.Row.Height = 480;
            cell133.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell134 = row13.CreateCell(3);
            cell134.SetCellValue(fkr);
            cell134.Row.Height = 480;
            cell134.CellStyle = style2;
            sheet.AutoSizeColumn(0);

            ICell cell135 = row13.CreateCell(4);
            cell135.SetCellValue("反馈时间");
            cell135.Row.Height = 480;
            cell135.CellStyle = style1;
            sheet.AutoSizeColumn(0);

            ICell cell136 = row13.CreateCell(5);
            cell136.SetCellValue(fksj);
            cell136.Row.Height = 480;
            cell136.CellStyle = style2;
            sheet.AutoSizeColumn(0);
            #endregion






            //IRow row = sheet.CreateRow(1);
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    ICell cell = row.CreateCell(i);
            //    cell.SetCellValue(dt.Columns[i].ColumnName);
            //    cell.Row.Height = 500;
            //    cell.CellStyle = style2;
            //    sheet.AutoSizeColumn(0);
            //}

            ////数据
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    IRow row1 = sheet.CreateRow(i + 2);
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {
            //        ICell cell = row1.CreateCell(j);
            //        cell.SetCellValue(dt.Rows[i][j].ToString());
            //        cell.Row.Height = 400;
            //        cell.CellStyle = style2;
            //    }
            //}

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }
    }

    public static DataTable GetDataTable(string filepath)
    {
        var dt = new DataTable("xls");
        if (filepath.Last() == 's')
        {
            dt = x2003.ExcelToTableForXLS(filepath);
        }
        else
        {
            dt = x2007.ExcelToTableForXLSX(filepath);
        }
        return dt;
    }



    
}